import re # 正则表达式,进行文字匹配
from applications.extensions import db
from sqlalchemy import text

'''
    select t.id , group2.name , t.sort,t.second_id,t.mian_id,group2.type
    from ye_many_relationship t
    left join (
        select t.* from ye_group t where t.type = '2'
    ) group2 on group2.id = t.second_id
    ###<%=where,and%>
        and t.mian_id = :mian_id
        ***<%=keyword%> and group2.name like '%'|| :keyword ||'%' ***
        ***<%=key%> and group2.name like '%'|| :key ||'%' ***
    ###
    order by t.sort asc

    ###<%=and%>包裹的内容, 遇到第一个<%= 与 %>字符包裹的内容会删除掉;  示例中会删除掉第一个and字符,并添加where
    ***<%=keyword%>包裹的内容, 如果keyword属性为null或者字符为空, 会隐藏这段字符串;
    
    
    
    sql处理帮助类,  实现类似mybatis的sql处理；
'''
class SqlUtil:

    def __init__(self):
        # 处理 ### 相关
        self.firstDeleteRe = re.compile(r'###((.|\n)*?)###')
        self.firstDeleteOriginRe = re.compile(r'###<%.*?%>((.|\n)*?)###')
        self.firstDeleteTagOneStr = None
        self.firstDeleteTagTwoRe = None
        # 处理 *** 相关
        self.checkRemoveRe = re.compile(r'\*\*\*((.|\n)*?)\*\*\*')
        self.checkRemoveOriginRe = re.compile(r'\*\*\*<%.*?%>((.|\n)*?)\*\*\*')
        self.tagRe = re.compile(r'<%=(.*?)%>')
        pass
    
    '''
        动态变更正则初始化匹配内容
    '''
    def refactoringRegularization(self,tag1=None,tag2=None,tagStr=None):
        reStr1 = None
        reStr2 = None
        # print(tagStr)
        if tagStr is not None and len(tagStr) > 0 :
            handleStr = tagStr.split(',')
            reStr1=handleStr[0]
            reStr2 = r'^(.|\n)*?({content})'.format(content=handleStr[1])
        else : 
            reStr1 = tag1
            reStr2 = r'^(.|\n)*?({content})'.format(content=tag2)
        self.firstDeleteTagOneStr = reStr1
        self.firstDeleteTagTwoRe = re.compile(reStr2)
        # print(self.firstDeleteTagTwoRe)


    '''
        判断字符串是否为空， 包含换行符，空格都判断为空；；
    '''
    def validStrNone(self,validStr):
        # 使用 re.sub 将多个连续的空白字符替换为单个空格
        newText = re.sub(r'(\s|\n)*', '', validStr).strip()
        # print(newText)
        #re.fullmatch 是 Python 3.4 引入的一个函数，它确保整个字符串都匹配给定的正则表达式模式。
        pattern = r'^$'
        if re.fullmatch(pattern, newText):
            return True
        else:
            return False
    


    '''
        处理 *** 包裹sql语句；
    '''
    def dynamicHandleCheckRemoveReSql(self,baseSql,params):
        finallySql = baseSql
        # 获取 removeReIter- *** 包裹的内容信息：
        removeReIter = re.finditer(self.checkRemoveRe, finallySql)        
        for removeRe in removeReIter:
            # childOriginStr2 -原始字符串； childStr2 - copy原始字符串用于字符串处理得到最终的sql
            childOriginStr2 = removeRe.group()
            childStr2 = childOriginStr2
            # 获取该 *** 字符串中的 tagStr-tag信息 
            tagStr2 = re.findall(self.tagRe,childOriginStr2)[0]
            # 如果传参params中存在 tagStr2 这个key ； 则显示该字符串； 否则隐藏改字符串；
            # print(tagStr2)
            # if(params[tagStr2] is not None):
            if(params.get(tagStr2) is not None):
                # 获取***中的不包含 标志的原始字符串信息；
                content = re.findall(self.checkRemoveOriginRe, childOriginStr2)[0][0]
                # print(re.findall(self.checkRemoveRe, childOriginStr2))
                # print(content[0])
                # 给content 加空格,防止字符串多次处理单词连在一起;
                # content = ' '  + content + ' '
                # 处理字符串, 匹配第一次查询到的字符串即可; 替换为content
                finallySql = re.sub(re.escape(childOriginStr2),content,finallySql,1)
                # print(content)
                # print(childOriginStr2)
                # print(childStr)
            else:
                # 处理字符串, 匹配第一次查询到的字符串即可; 删除改字符串
                finallySql = re.sub(re.escape(childOriginStr2),'',finallySql,1)
        return finallySql
    '''
        处理 ### 包裹sql语句,
    '''    
    def dynamicHandleSql(self,baseSql,params):    
        # 处理###包裹的内容
        # deleteStrList = re.match(self.firstDeleteRe, baseSql)
        finallySql = baseSql
        # 获取 deleteReIter- ### 包裹的内容信息
        deleteReIter = re.finditer(self.firstDeleteRe, baseSql)
        deleteConut=0
        # 如果存在 ###包裹内容， 进入如下逻辑；
        for deleteRe in deleteReIter:
            deleteConut = deleteConut + 1
            # childOriginStr -原始字符串； childStr - copy原始字符串用于字符串处理得到最终的sql
            childOriginStr = deleteRe.group()
            childStr = childOriginStr
            # print(childStr)
            # 处理 *** 字符串 
            childStr = self.dynamicHandleCheckRemoveReSql(childStr,params)
            # 获取中childStr处理后字符串中的 ### 中字符串, 不包含***这些标志字符;
            # print(childStr)
            content = re.findall(self.firstDeleteOriginRe, childStr)[0][0]
            # 获取该 ### 字符串中的 tagStr-tag信息 
            if(not self.validStrNone(content)):
                tagStr = re.findall(self.tagRe,childOriginStr)[0]
                self.refactoringRegularization(tagStr=tagStr)
                # print(content)
                content = re.sub(self.firstDeleteTagTwoRe,'',content)            
                content = self.firstDeleteTagOneStr + ' ' + content 
            # 给content 加空格,防止字符串多次处理单词连在一起;
            # content = ' '  + content + ' '
            # 处理字符串, 匹配第一次查询到的字符串即可; 替换为content
            # print(content)
            finallySql = re.sub(re.escape(childOriginStr),content,finallySql,1)
        # 如果没有 ### 包裹内容；则直接处理 *** 包裹内容；
        if deleteConut == 0 : 
            finallySql = self.dynamicHandleCheckRemoveReSql(baseSql,params)
        return finallySql

    '''
        处理sql语句,并执行;
    '''
    def dynamicHandleSqlRun(self, baseSql, params):
        finalySql = self.dynamicHandleSql(baseSql,params)
        cursor = db.session.execute(text(finalySql),params) 
        result = cursor.fetchall()
        return result

    def dynamicHandleSqlRunPage(self, baseSql, params):
        finalySql = self.dynamicHandleSql(baseSql,params)
        pageSql = '''select t.* from ({content}) t limit :limit offset :page '''.format(content=finalySql)
        sqlCount = '''select count(t.id) from ({content}) t'''.format(content=finalySql)
        cursor = db.session.execute(text(pageSql),params) 
        cursor2 = db.session.execute(text(sqlCount),params) 
        result = cursor.fetchall()
        count = cursor2.fetchall()[0][0]
        return dict(data = result, total = count)
